<?php
namespace Home\Controller;
class ExportController extends CommonController {
public function _initialize()
{
	set_time_limit(0);
	ini_set('max_execution_time', '0');
	ini_set('memory_limit','500M');
	ob_end_clean();
	$this->Is_login();        //检测用户登录信息,如果错误直接返回错误,终止程序
	// $this->Is_power("money");        //检测用户权限,如果错误直接返回错误,终止程序
	$this->assign('heardadminData',session('heardadminData'));
	$this->start = "2018-09-03";
	$this->end = "2018-09-11";
	$this->time = date('Ymd',time());
}
/*------------------------------------------*/
public function unorder_old()
{
	$filename = $this->time."异常订单数据";
	$db_theorder = M("theorder");
	$excelarr = array("订单编号","标题","字数","级别","下单时间","预计时间","交付时间","异常状态","主播编号","是否自选","是否背景音","是否加急","订单类型");
	// 查询数据
	$where['releasetime'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$orderlist = M("theorder o")->join('__SYSTEMSETUP__ s ON o.level = s.id','LEFT')->where($where)->where("(state=4 or state>5) and isdummy!=1 and is_pay=1")->field("o.*,s.message level_type,s.money userlevel")->select();

	$where1['receivingtime'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$theorderinfolist = M("theorderinfo")->where($where1)->where("issign!=0")->select();
	$excellist = array();
	// p($orderlist);exit();
	// echo count($orderlist);exit();
	foreach ($orderlist as $k => &$order){
		$usermoney = $order['money'];
		if ($order['demand'] == 2) {
			$usermoney = $order['money']+$order['premium'];
		}
		if ($order['bgmid']!=0) {
			$usermoney = $usermoney+10;
		}
		switch ($order['state']) {
			case '6':$order['state_type']="无人、未接单";break;
			case '7':$order['state_type']="超时未能完成";break;
			case '8':$order['state_type']="主播主动取消";break;
			default:$order['state_type']="正常";break;
		}
		switch ($order['attribute']) {
			case '0':$order['order_type']="实时单";break;
			case '1':$order['order_type']="签约单";break;
			case '2':$order['order_type']="派发单";break;
		}
		if ($order['attribute']!=0 and ($order['state']==6 or $order['state']==8)) {
			foreach ($theorderinfolist as $key => $value) {
				if ($order['id'] = $value['oid']) {
					$order['uuid'] = $value['uuid'];
				}
			}
		}
		$excellist[$k]['id'] = $order['id'];
		$excellist[$k]['title'] = $order['title'];
		$excellist[$k]['words'] = $order['words'];
		$excellist[$k]['level'] = $order['level_type'];
		$excellist[$k]['releasetime'] = date("Y-m-d H:i:s",$order['releasetime']);
		$excellist[$k]['ordertime'] = date("Y-m-d H:i:s",$order['releasetime']+$order['expectedtime']+$order['circulationtime']);
		$excellist[$k]['linkaudiotime'] = date("Y-m-d H:i:s",$order['linkaudiotime']);
		$excellist[$k]['state_type'] = $order['state_type'];
		$excellist[$k]['uuid'] = $order['uuid'];
		$excellist[$k]['words'] = $order['words'];
		$excellist[$k]['ischose'] = $order['ischose']==1?"是":"否";
		$excellist[$k]['bgmid'] = $order['bgmid']!=0?"是":"否";
		$excellist[$k]['demand2'] = $order['demand']==2?"是":"否";
		$excellist[$k]['order_type'] = $order['order_type'];
	}
	output_excel($filename.time(),$excelarr,$excellist);
}
public function unorder()
{
	$filename = $this->time."异常订单数据";
	$db_theorder = M("theorder");
	$excelarr = array("订单编号","标题","字数","级别","下单时间","预计时间","异常状态","主播编号","是否自选","是否背景音","是否加急","订单类型");
	// 查询数据
	$where['pay_time'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$orderlist = M("order o")->join('__SYSTEMSETUP__ s ON o.level = s.id','LEFT')->where($where)->where("(status=7 or status>9) and sub_type!=1 and pay_status=1 and o.id>97274")->field("o.*,s.message level_type,s.money userlevel")->select();
	$excellist = array();
	// p($orderlist);exit();
	// echo count($orderlist);exit();
	foreach ($orderlist as $k => &$order){
		$usermoney = $order['prcie'];
		if ($order['requirement'] == 3) {
			$usermoney = $order['prcie']+$order['increase_price'];
		}
		if ($order['bgm_id']!=0) {
			$usermoney = $usermoney+10;
		}
		switch ($order['status']) {
			case '11':$order['status_type']="无人、未接单";break;
			case '13':$order['status_type']="超时未能完成";break;
			case '15':$order['status_type']="主播主动取消";break;
			default:$order['status_type']="正常";break;
		}
		switch ($order['type']) {
			case '1':$order['order_type']="实时单";break;
			case '2':$order['order_type']="签约单";break;
			case '3':$order['order_type']="派发单";break;
		}
		if ($order['type']!=1 and $order['status']!=13) {
			$order['uuid'] = array_keys(json_decode(RedisGet('order'.$order['id']),TRUE)['list'])['0'];
		}
		$excellist[$k]['id'] = $order['id'];
		$excellist[$k]['title'] = $order['title'];
		$excellist[$k]['words'] = $order['words'];
		$excellist[$k]['level'] = $order['level_type'];
		$excellist[$k]['pay_time'] = date("Y-m-d H:i:s",$order['pay_time']);
		$excellist[$k]['ordertime'] = date("Y-m-d H:i:s",$order['pay_time']+$order['deliver_time']+$order['delay_time']);
		$excellist[$k]['audio_upload_time'] = date("Y-m-d H:i:s",$order['audio_upload_time']);
		$excellist[$k]['status_type'] = $order['status_type'];
		$excellist[$k]['uuid'] = $order['uuid'];
		$excellist[$k]['words'] = $order['words'];
		$excellist[$k]['ischose'] = $order['sub_type']==3?"是":"否";
		$excellist[$k]['bgmid'] = $order['bgm_id']!=0?"是":"否";
		$excellist[$k]['demand2'] = $order['requirement']==3?"是":"否";
		$excellist[$k]['order_type'] = $order['order_type'];
	}
	output_excel($filename.time(),$excelarr,$excellist);
}
/*-----------------------------------------------------------------*/
// 实时订单
public function order_old()
{
	$filename = $this->time."实时订单数据";
	$db_theorder = M("theorder");
	$excelarr = array("订单编号","自媒体名称","订单等级","字数","元/百字","背景音(20元/条)","自选(+20%)","预约(-20%)","加急(+50%)","支付金额","支付方式","下单时间","交单时间","接单主播编号","主播收入","元/百字");
	// 查询数据
	$where['releasetime'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$orderlist = M("theorder o")->join('__SYSTEMSETUP__ s ON o.level = s.id','LEFT')->where($where)->where("(state=4) and o.attribute=0 and isdummy!=1")->field("o.*,s.message level_type,s.money userlevel")->select();
	$excellist = array();
	// p($orderlist);exit();
	// echo count($orderlist);exit();
	foreach ($orderlist as $k => &$order){
		$usermoney = $order['money'];
		if ($order['demand'] == 2) {
			$usermoney = $order['money']+$order['premium'];
		}
		if ($order['bgmid']!=0) {
			$usermoney = $usermoney+10;
		}
		switch ($order['pay_type']) {
			case '1':$order['pay_type']="微信";break;
			case '2':$order['pay_type']="支付宝";break;
			case '3':$order['pay_type']="字数";break;
			case '5':$order['pay_type']="余额";break;
			default:$order['pay_type']="余额";break;
		}
		$excellist[$k]['id'] = $order['id'];
		$excellist[$k]['media_name'] = $order['mname'];
		$excellist[$k]['level'] = $order['level_type'];
		$excellist[$k]['words'] = $order['words'];
		$excellist[$k]['levelmoney'] = $order['userlevel']*2;
		$excellist[$k]['bgmid'] = $order['bgmid']!=0?"是":"否";
		$excellist[$k]['ischose'] = $order['ischose']==1?"是":"否";
		$excellist[$k]['demand3'] = $order['demand']==3?"是":"否";
		$excellist[$k]['demand2'] = $order['demand']==2?"是":"否";
		$excellist[$k]['paymoney'] = $usermoney*2;
		$excellist[$k]['pay_type'] = $order['pay_type'];
		$excellist[$k]['releasetime'] = date("Y-m-d H:i:s",$order['releasetime']);
		$excellist[$k]['linkaudiotime'] = date("Y-m-d H:i:s",$order['linkaudiotime']);
		$excellist[$k]['uuid'] = $order['uuid'];
		$excellist[$k]['usermoney'] = $usermoney;
		$excellist[$k]['userlevel'] = $order['userlevel'];
	}
	output_excel($filename.time(),$excelarr,$excellist);
}
public function order()
{
	$filename = $this->time."实时订单数据";
	$start_time = I('post.start')?I('post.start'):$this->start;
	$end_time = I('post.end')? I('post.end'):$this->end;
	$db_theorder = M("order");
	$excelarr = array("订单编号","自媒体名称","唯一识别码","订单等级","字数","元/百字","背景音(20元/条)","自选(+20%)","预约(-20%)","加急(+50%)","支付金额","支付中赠送金额","剩余赠送金额","剩余余额","支付方式","支付订单号","下单时间","交单时间","接单主播编号","主播收入","元/百字","类型","性别");
	// 查询数据
	$where['pay_time'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
	$where['status'] = 8;
	$orderlist = M("order o")
		->join('__SYSTEMSETUP__ s ON o.level = s.id','LEFT')
		->join('__MEDIA__ m on m.id = o.media_id')
		->where($where)->where("o.type=1 and sub_type!=1")
		->field("o.*,s.message level_type,money userlevel,m.wid")
		->select();
	$w_where["creationtime"] = array("BETWEEN",array($this->start,$this->end));
	$wemediaincome = M('wemediaincome')->where($w_where)->where(["status"=>['in',[3,4,5]]])->select();
	$wemediaincome_arr = array();//支付记录数据
	foreach($wemediaincome as $we){
		$wemediaincome_arr[$we['oid']] = $we;
	}
	$excellist = array();
	// p($orderlist);exit();
	// echo count($orderlist);exit();
	foreach ($orderlist as $k => &$order){
		$usermoney = $order['price'];
		if ($order['requirement'] == 3) {
			$usermoney = $order['price']+$order['increase_price'];
		}
		if ($order['bgm_id']!=0) {
			$usermoney = $usermoney+10;
		}
		$order['order_number'] = $order['id'];//订单编号
		switch ($order['pay_type']) {
			case '1':
				$order['pay_type_name']="微信";
				$messageobj = json_decode(M('orderqrcode')->where("number = '".$order[content]."' or (number = 0 and order_sn = $order[id])")->where(['type'=>1,'paytype'=>['in',[1,3,4]]])->getField('message'));
				$order['order_number'] = $messageobj->transaction_id;
				break;
			case '2':
				$order['pay_type_name']="支付宝";
				$messageobj = json_decode(M('orderqrcode')->where(['type'=>1,'paytype'=>2])->where("(number = '".$order[content]."') or (number = 0 and order_sn = $order[id])")->getField('message'));
				$order['order_number'] = $messageobj->trade_no;
				break;
			case '3':$order['pay_type_name']="字数";break;
			case '5':$order['pay_type_name']="余额";break;
			default:$order['pay_type_name']="余额";break;
		}
		switch ($order['category']){//类型 1 讲述 2 情感 3 演绎 4 新闻  5 英文
			case 1:$order['category'] = "讲述";break;
			case 2:$order['category'] = "情感";break;
			case 3:$order['category'] = "演绎";break;
			case 4:$order['category'] = "新闻";break;
			case 5:$order['category'] = "英文";break;
		}
		switch($order['sex']){
			case 0:$order['sex_name'] = "全部";break;
			case 1:$order['sex_name'] = "男";break;
			case 2:$order['sex_name'] = "女";break;
		}

		$excellist[$k]['id'] = $order['id'];
		$excellist[$k]['media_name'] = $order['media_name'];
		$excellist[$k]['wid'] = $order['wid'];
		$excellist[$k]['level'] = $order['level_type'];
		$excellist[$k]['words'] = $order['words'];
		$excellist[$k]['levelmoney'] = $order['userlevel']*2;
		$excellist[$k]['bgm_id'] = $order['bgm_id']!=0?"是":"否";
		$excellist[$k]['sub_type'] = $order['sub_type']==3?"是":"否";
		$excellist[$k]['requirement2'] = $order['requirement']==2?"是":"否";
		$excellist[$k]['requirement3'] = $order['requirement']==3?"是":"否";
		$excellist[$k]['paymoney'] = $usermoney*2;
		$excellist[$k]['give_money'] = $wemediaincome_arr[$order['id']]['grantmoney'];//赠送金额
		$excellist[$k]['give_surplus_money'] = $wemediaincome_arr[$order['id']]['grantbalance'];//赠送剩余
		$excellist[$k]['recharge_surplus_money'] = $wemediaincome_arr[$order['id']]['balance'];//剩余余额
		$excellist[$k]['pay_type_name'] = $order['pay_type_name'];//时长
		$excellist[$k]['order_number'] = $order['order_number'];//支付订单号
		$excellist[$k]['pay_time'] = date("Y-m-d H:i:s",$order['pay_time']);
		$excellist[$k]['audio_upload_time'] = date("Y-m-d H:i:s",$order['audio_upload_time']);
		$excellist[$k]['user_id'] = $order['user_id'];
		$excellist[$k]['usermoney'] = $usermoney;
		$excellist[$k]['userlevel'] = $order['userlevel'];
		$excellist[$k]['category'] = $order['category'];
		$excellist[$k]['sex_name'] = $order['sex_name'];
	}
	output_excel($filename.time(),$excelarr,$excellist);
}
/*-----------------------------------------------------------------*/

/*-----------------------------------------------------------------*/
// 签约订单
public function signorder()
{
	$filename = $this->time."签约订单数据";
	$excelarr = array("订单编号","自媒体名称","唯一识别码","订单等级","订单字数","赠送字数","赠送剩余字数","充值剩余字数","元/百字","订单金额","剩余金额","wav时长(秒)","wav时长(小时)","下单时间","交单时间","主播编号","主播收入","签约价格(元/小时)","类型","背景音");
	// 查询数据
	$where['pay_time'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$orderlist = M("order o")
		->join('__MEMBER__ u ON o.user_id = u.uuid','LEFT')
		->join('__SYSTEMSETUP__ s ON o.level = s.id','LEFT')
		->join('__MEDIA__ m on m.id = o.media_id','LEFT')
		->where($where)->where("(o.status=8) and o.type=2 and sub_type!=1")
		->field("o.*,s.message level_type,money userlevel,proportion,m.wid,m.mmid")
		->select();
	$excellist = array();
	$musictime = M("musictime")->where(array("type"=>1,"state"=>1))->select();
	$wemediaincome = M('wemediaincome')->where('status = 7')->select();
	//查询出9月3号15：00之后，充值记录 算出单价 如果订单上没有单价 就用这个单价
	$where111['UNIX_TIMESTAMP(creationtime)'] = ['gt',1535958000];
	$where111['status'] = ['in',[6,13]];
	$sum_list = M('wemediaincome')->where($where111)->group('wid')->field('wid,sum(money) money,sum(grantmoney) grantmoney,sum(signmoney) signmoney')->select();
	$sum_arr = array();
	foreach($sum_list as $sum){
		$sum_arr[$sum['wid']] = $sum;
	}
	$musictime_arr = array();
	foreach($musictime as $mu){
		$musictime_arr[$mu['oid']] = $mu;
	}
	$wemediaincome_arr = array();//支付记录数据
	foreach($wemediaincome as $we){
		$wemediaincome_arr[$we['oid']] = $we;
	}
	foreach ($orderlist as $k => &$order){
		$usermoney = $order['price'];
		if ($order['requirement'] == 3) {
			$usermoney = $order['price']+$order['increase_price'];
		}
		if ($order['bgm_id']>0) {
			$order['bgm']='有';
			$usermoney = $usermoney+10;
		}else{
			$order['bgm']='无';
		}
		switch ($order['pay_type']) {
			case '1':$order['pay_type']="微信";break;
			case '2':$order['pay_type']="支付宝";break;
			case '3':$order['pay_type']="字数";break;
			case '5':$order['pay_type']="余额";break;
			default:$order['pay_type']="余额";break;
		}
		switch ($order['category']){//类型 1 讲述 2 情感 3 演绎 4 新闻  5 英文
			case 1:$order['category'] = "讲述";break;
			case 2:$order['category'] = "情感";break;
			case 3:$order['category'] = "演绎";break;
			case 4:$order['category'] = "新闻";break;
			case 5:$order['category'] = "英文";break;
		}
		//单价
		if($wemediaincome_arr[$order['id']]['unit_price']){//wemediaincome表中有单价
			$order['unit_price'] = $wemediaincome_arr[$order['id']]['unit_price'];
		}else{
			$order['unit_price'] = $sum_arr[$order['mmid']]['signmoney']*100/($sum_arr[$order['mmid']]['money'] + $sum_arr[$order['mmid']]['grantmoney']);

		}
		$order['order_money'] = ($musictime_arr[$order['id']]['musictime']*$order['proportion'])/3600;//订单金额
		$excellist[$k]['id'] = $order['id'];
		$excellist[$k]['media_name'] = $order['media_name'];
		$excellist[$k]['wid'] = $order['wid'];
		$excellist[$k]['level'] = $order['level_type'];
		$excellist[$k]['words'] = $order['words'];
		$excellist[$k]['give_word'] = $wemediaincome_arr[$order['id']]['grantmoney'];//赠送字数
		$excellist[$k]['give_surplus_word'] = $wemediaincome_arr[$order['id']]['grantbalance'];//赠送剩余字数
		$excellist[$k]['recharge_surplus_word'] = $wemediaincome_arr[$order['id']]['balance'];//充值剩余字数
		$excellist[$k]['unit_price'] = sprintf("%.2f",$order['unit_price']);
		$excellist[$k]['order_money'] = sprintf("%.2f",($order['unit_price']*$order['words'])/100);
		$excellist[$k]['surplus_money'] = '';
//			$order['order_money']?sprintf("%.2f",($order['give_surplus_word']+$order['recharge_surplus_word'])/$excellist[$k]['unit_price']):'';
		$excellist[$k]['musictime'] = $musictime_arr[$order['id']]['musictime'];
		$excellist[$k]['musichour'] = sprintf("%.2f",$musictime_arr[$order['id']]['musictime']/3600);
		$excellist[$k]['pay_time'] = date("Y-m-d H:i:s",$order['pay_time']);
		$excellist[$k]['audio_upload_time'] = date("Y-m-d H:i:s",$order['audio_upload_time']);
		$excellist[$k]['user_id'] = $order['user_id'];
		$excellist[$k]['usermoney'] = sprintf("%.2f",$order['order_money']);
		$excellist[$k]['proportion'] = $order['proportion'];
		$excellist[$k]['category'] = $order['category'];
		$excellist[$k]['bgm'] = $order['bgm'];
	}
	output_excel($filename.time(),$excelarr,$excellist);
}
/*-----------------------------------------------------------------*/
/*-----------------------------------------------------------------*/
// 派发订单
public function sendorder_old()
{
	$filename = $this->time."派发订单数据";
	$excelarr = array("订单编号","订单名称","元/条","下单时间","交单时间","主播编号","主播收入");
	// 查询数据
	$where['releasetime'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$orderlist = M("theorder o")->where($where)->where("(o.state=3 or o.state=4) and (issend=1) ")->field("o.*")->select();
	$adminlist = M("admin")->select();
	$excellist = array();
	// p($orderlist);exit();
	// echo count($orderlist);exit();
	foreach ($orderlist as $k => &$order){
		if ($order['adminid']!=0) {
			foreach ($adminlist as $key => $value) {
				if ($order['adminid'] == $value['heard_admin_id']) {
					$user = $value['heard_adminname'];
				}
			}
			$money = "4";
		}else{
			$user = $order['uuid'];
			$money = "3";
		}
		$excellist[$k]['id'] = $order['id'];
		$excellist[$k]['title'] = $order['title'];
		$excellist[$k]['money'] = "4.5";
		$excellist[$k]['pay_time'] = date("Y-m-d H:i:s",$order['releasetime']);
		$excellist[$k]['audio_upload_time'] = date("Y-m-d H:i:s",$order['linkaudiotime']);
		$excellist[$k]['user_id'] = $user;
		$excellist[$k]['usermoney'] = $money;
	}
	// p($excellist);exit();
	output_excel($filename.time(),$excelarr,$excellist);
}

//新的派发订单
public function sendorder()
{
	$filename = $this->time."派发订单数据";
	$excelarr = array("订单编号","订单名称","元/条","下单时间","交单时间","主播编号","主播收入");
	// 查询数据
	$where['pay_time'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$orderlist = M("order o")->where($where)->where("(o.status=8) and (type=3 or type=4) ")->field("o.*")->select();
	$adminlist = M("admin")->select();
	$excellist = array();
	// p($orderlist);exit();
	// echo count($orderlist);exit();
	foreach ($orderlist as $k => &$order){
		if ($order['adminid']!=0) {
			foreach ($adminlist as $key => $value) {
				if ($order['adminid'] == $value['heard_admin_id']) {
					$user = $value['heard_adminname'];
				}
			}
			$money = "4";
		}else{
			$user = $order['user_id'];
			$money = "3";
		}
		$excellist[$k]['id'] = $order['id'];
		$excellist[$k]['title'] = $order['title'];
		$excellist[$k]['money'] = "4.5";
		$excellist[$k]['pay_time'] = date("Y-m-d H:i:s",$order['pay_time']);
		$excellist[$k]['audio_upload_time'] = date("Y-m-d H:i:s",$order['audio_upload_time']);
		$excellist[$k]['user_id'] = $user;
		$excellist[$k]['usermoney'] = $money;
	}
	// p($excellist);exit();
	output_excel($filename.time(),$excelarr,$excellist);
}
/*-----------------------------------------------------------------*/
/*-----------------------------------------------------------------*/
// 主播收入
public function member_income_old()
{
	set_time_limit(0);
	ini_set('max_execution_time', '0');
	ini_set('memory_limit','500M');
	ob_end_clean();
	$filename = $this->time."主播收入数据";
	$db_theorder = M("theorder");
	$excelarr = array("主播编号","主播昵称","订单类型","实时总单量","包含:背景音单","包含:自选单","包含:预约单","包含:加急单","实时单总收入","签约单量","签约收入","派发单单量","派发单收入","奖励","总订单量/月","总时长(认证主播)","总收入/月");
	// 查询数据
	$userlist = M("member")->where("income>0 or alwaystime>0")->field("uuid,nickname,authentication,proportion,income,balance")->select();
	$excellist = array();
	$where1['time'] = array("BETWEEN",array(strtotime($this->start)+86400+120,strtotime($this->end)+86400+120));
	$where['releasetime'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$orderlist = $db_theorder->where($where)->where("state = 4")->field("id,uuid,attribute,demand,money,premium,issend,originalaudio,ischose,bgmid")->select();
	foreach ($orderlist as $key => $value) {
		$userorder[$value['uuid']][] = $value;
	}
	// p($userlist);exit();
	// echo count($userorder);exit();
	foreach ($userlist as $k => &$user){
		$user['allordercount'] = 0;//所有单
		$user['theordercount'] = 0;//实时总单量
		$user['bgmordercount'] = 0;//背景音单单量
		$user['choseordercount'] = 0;//自选单单量
		$user['demand3ordercount'] = 0;//预约单单量
		$user['demand2ordercount'] = 0;//加急单单量
		$user['theorderincome'] = 0;//普通单收入
		$user['txordercount'] = 0;//派单数量
		$user['signordercount'] = 0;//签约单数量
		$user['signorderwordcount'] = 0;//签约单总字数
		$user['durationtime'] = 0;//wav时长
		if ($userorder[$user['uuid']]) {
			foreach ($userorder[$user['uuid']] as $key => &$order) {
				$user['allordercount'] += 1;
				if ($order['attribute']==0) {
					$user['theordercount'] += 1;
					if ($order['demand']==2) {
						$user['theorderincome'] += $order['money']+$order['premium'];
					}else{
						$user['theorderincome'] += $order['money'];
					}
					if ($order['bgmid']!=0) {
						$user['bgmordercount'] += 1;
						$user['theorderincome'] += 10;
					}
					if ($order['ischose']==1) {
						$user['choseordercount'] += 1;
					}
					switch ($order['demand']) {
						case '2':
							$user['demand2ordercount'] += 1;
							break;
						case '3':
							$user['demand3ordercount'] += 1;
							break;
					}
				}
				if ($order['issend']==1) {
					$user['txordercount'] += 1;
				}
				if ($order['attribute']==1) {
					$user['signordercount'] += 1;
					// $user['signorderwordcount'] += $order['words'];
					$user['durationtime'] += M("musictime")->where(array("oid"=>$order['id'],"type"=>1,"state"=>1))->find()['musictime']+0;
					// $user['durationtime'] += (int)json_decode(do_curl($order['originalaudio'].'?avinfo'),true)['format']['duration'];
				}
			}
		}
		$user['txordermoney'] = M("income")->where($where1)->where(array("uuid"=>$user['uuid'],"type"=>5))->sum("amount")+0;
		$excellist[$k]['uuid'] = $user['uuid'];
		$excellist[$k]['nickname'] = $user['nickname'];
		$excellist[$k]['ordertype'] = ""/*$user['ordertype']*/;
		$excellist[$k]['theordercount'] = $user['theordercount'];
		$excellist[$k]['bgmordercount'] = $user['bgmordercount'];
		$excellist[$k]['choseordercount'] = $user['choseordercount'];
		$excellist[$k]['demand3ordercount'] = $user['demand3ordercount'];
		$excellist[$k]['demand2ordercount'] = $user['demand2ordercount'];
		$excellist[$k]['theorderincome'] = $user['theorderincome'];
		$excellist[$k]['signordercount'] = $user['signordercount'];
		$excellist[$k]['proportionmoney'] = floor($user['durationtime']/3600)*$user['proportion'];
		$excellist[$k]['txordercount'] = $user['txordercount'];
		$excellist[$k]['txordercount3'] = $user['txordercount']*3;
		$excellist[$k]['txordermoney'] = $user['txordermoney'];
		$excellist[$k]['allordercount'] = $user['allordercount'];
		$excellist[$k]['durationtime'] = $user['durationtime'];//时长
		$excellist[$k]['allmoney'] = ($user['txordercount']*3)+(floor($user['durationtime']/3600)*$user['proportion'])+$user['theorderincome'];
	}
	output_excel($filename.time(),$excelarr,$excellist);
}

//主播收入月总表
public function member_income_new()
{
	set_time_limit(0);
	ini_set('max_execution_time', '0');
	ini_set('memory_limit','500M');
	ob_end_clean();
	$filename = $this->time."主播收入月总数据";
	$db_theorder = M("order");
	$excelarr = array("主播编号","主播昵称","订单类型","实时总单量","包含:背景音单","包含:自选单","包含:预约单","包含:加急单","实时单总收入","签约单量","签约收入","派发单单量","派发单收入","奖励","总订单量/月","总时长(认证主播)","总收入/月");
	// 查询数据
	$userlist = M("member")->where("income>0 or alwaystime>0")->field("uuid,nickname,authentication,proportion,income,balance,alwaystime")->select();
	$excellist = array();
	$where['pay_time'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$where1['time'] = array("BETWEEN",array(strtotime($this->start)+86400+120,strtotime($this->end)+86400+120));
	$orderlist = $db_theorder->where($where)->where("status = 8")->field("id,user_id uuid,type,sub_type,requirement,price,increase_price,bgm_id")->select();
	foreach ($orderlist as $key => $value) {
		$userorder[$value['uuid']][] = $value;
	}
//	 p($userorder);exit();
	// echo count($userorder);exit();
	foreach ($userlist as $k => &$user){
		$user['allordercount'] = 0;//所有单
		$user['theordercount'] = 0;//实时总单量
		$user['bgmordercount'] = 0;//背景音单单量
		$user['choseordercount'] = 0;//自选单单量
		$user['demand3ordercount'] = 0;//预约单单量
		$user['demand2ordercount'] = 0;//加急单单量
		$user['theorderincome'] = 0;//普通单收入
		$user['txordercount'] = 0;//派单数量
		$user['signordercount'] = 0;//签约单数量
		$user['signorderwordcount'] = 0;//签约单总字数
		$user['durationtime'] = 0;//wav时长
		if ($userorder[$user['uuid']]) {
			foreach ($userorder[$user['uuid']] as $key => &$order) {
				$user['allordercount'] += 1;
				if ($order['type']==1) {
					$user['theordercount'] += 1;
					if ($order['requirement']==3) {
						$user['theorderincome'] += $order['price']+$order['increase_price'];
					}else{
						$user['theorderincome'] += $order['price'];
					}
					if ($order['bgm_id']!=0) {
						$user['bgmordercount'] += 1;
						$user['theorderincome'] += 10;
					}
					if ($order['sub_type']==3) {
						$user['choseordercount'] += 1;
					}
					switch ($order['requirement']) {
						case '3':
							$user['demand2ordercount'] += 1;
							break;
						case '2':
							$user['demand3ordercount'] += 1;
							break;
					}
				}
				if ($order['type']==3) {
					$user['txordercount'] += 1;
				}
				if ($order['type']==2) {
					$user['signordercount'] += 1;
					// $user['signorderwordcount'] += $order['words'];
					$user['durationtime'] += M("musictime")->where(array("oid"=>$order['id'],"type"=>1,"state"=>1))->find()['musictime']+0;
					// $user['durationtime'] += (int)json_decode(do_curl($order['originalaudio'].'?avinfo'),true)['format']['duration'];
				}
			}
		}
		$user['txordermoney'] = M("income")->where($where1)->where(array("uuid"=>$user['uuid'],"type"=>5))->sum("amount")+0;
		$excellist[$k]['uuid'] = $user['uuid'];
		$excellist[$k]['nickname'] = $user['nickname'];
		$excellist[$k]['ordertype'] = ""/*$user['ordertype']*/;
		$excellist[$k]['theordercount'] = $user['theordercount'];
		$excellist[$k]['bgmordercount'] = $user['bgmordercount'];
		$excellist[$k]['choseordercount'] = $user['choseordercount'];
		$excellist[$k]['demand3ordercount'] = $user['demand3ordercount'];
		$excellist[$k]['demand2ordercount'] = $user['demand2ordercount'];
		$excellist[$k]['theorderincome'] = $user['theorderincome'];
		$excellist[$k]['signordercount'] = $user['signordercount'];
		$excellist[$k]['proportionmoney'] = floor($user['durationtime']/3600)*$user['proportion'];
		$excellist[$k]['txordercount'] = $user['txordercount'];
		$excellist[$k]['txordercount3'] = $user['txordercount']*3;
		$excellist[$k]['txordermoney'] = $user['txordermoney'];
		$excellist[$k]['allordercount'] = $user['allordercount'];
		$excellist[$k]['durationtime'] = $user['durationtime'];//时长
		$excellist[$k]['allmoney'] = ($user['txordercount']*3)+(floor($user['durationtime']/3600)*$user['proportion'])+$user['theorderincome'];
		// $excellist[$k]['alwaystime'] = $user['alwaystime'];//总时长
	}
	output_excel($filename.time(),$excelarr,$excellist);
}
/*-----------------------------------------------------------------*/
/*-----------------------------------------------------------------*/

	// 1支付宝充值
	// 2后台充值余额
	// 3余额支付
	// 4微信支付
	// 5支付宝支付
	// 6充值字数
	// 7字数支付
	// 8余额退款
	// 9微信退款
	// 10支付宝退款
	// 11赠送余额
	// 12赠送字数
// 自媒体
public function media()
{
	$filename = $this->time."自媒体数据";
	$excelarr = array("客户账号(手机号)","自媒体名称","唯一识别码","充值金额","赠送金额","使用金额","签约套餐类型","套餐金额","套餐字数","赠送字数","使用字数","创建时间");
	// 查询数据
	$where['creationtime'] = array("BETWEEN",array(($this->start),($this->end)));
	$medialist = M("media")->field("media_tel username,media_name,mmid,wid,create_time")->select();
	$wemediaincomelist = M("wemediaincome")->where($where)->select();
	$excellist = array();
	foreach ($medialist as $k => &$media){
		$media['money'] = 0;//充值金额
		$media['grantmoney'] = 0;//赠送金额
		$media['usemoney'] = 0;//使用金额
		$media['wordnumber'] = 0;//套餐字数
		$media['grantnumber'] = 0;//赠送字数
		$media['usewordnumber'] = 0;//使用字数
		foreach ($wemediaincomelist as $key => &$value) {
			if ($media['wid']==$value['wid']||$media['mmid']==$value['wid']) {
				switch ($value['status']) {
					case '1':if ($media['wid']==$value['wid']) {$media['money']+=$value['money'];}break;
					case '2':if ($media['wid']==$value['wid']) {$media['money']+=$value['money'];}break;
					case '3':if ($media['wid']==$value['wid']) {$media['usemoney']+=$value['money'];}break;
					case '4':if ($media['wid']==$value['wid']) {$media['usemoney']+=$value['money'];}break;
					case '5':if ($media['wid']==$value['wid']) {$media['usemoney']+=$value['money'];}break;
					case '6':if ($media['mmid']==$value['wid']) {$media['wordnumber']+=$value['money'];}break;
					case '7':if ($media['mmid']==$value['wid']) {$media['usewordnumber']+=$value['money'];}break;
					case '8':if ($media['wid']==$value['wid']) {$media['usemoney']-=$value['money'];}break;
					case '9':if ($media['wid']==$value['wid']) {$media['usemoney']-=($value['money']/100);}break;
					case '10':if ($media['wid']==$value['wid']) {$media['usemoney']-=$value['money'];}break;
					case '11':if ($media['wid']==$value['wid']) {$media['grantmoney']+=$value['money'];}break;
					case '12':if ($media['mmid']==$value['wid']) {$media['grantnumber']+=$value['money'];}break;
				}
			}
		}
	// p($media);
		$excellist[$k]['username'] = $media['username'];
		$excellist[$k]['media_name'] = $media['media_name'];
		$excellist[$k]['media_id'] = $media['wid'];
		$excellist[$k]['money'] = $media['money'];
		$excellist[$k]['grantmoney'] = $media['grantmoney'];
		$excellist[$k]['usemoney'] = $media['usemoney'];
		$excellist[$k]['taocanleixing'] = $media['taocanleixing'];
		$excellist[$k]['taocanjine'] = $media['taocanjine'];
		$excellist[$k]['wordnumber'] = $media['wordnumber'];
		$excellist[$k]['grantnumber'] = $media['grantnumber'];
		$excellist[$k]['usewordnumber'] = $media['usewordnumber'];
		$excellist[$k]['create_time'] = $media['create_time'];
	}//exit();
	// p($excellist);exit();
	output_excel($filename.time(),$excelarr,$excellist);
}
/*-----------------------------------------------------------------*/
/*-----------------------------------------------------------------*/
// 自媒体充值记录
	// 1支付宝充值
	// 2后台充值余额
	// 6充值字数
	// 11赠送余额
	// 12赠送字数
public function mediaincome()
{
	$filename = $this->time."自媒体充值数据";
	$excelarr = array("客户账号(手机号)","自媒体名称","充值金额","赠送金额","套餐字数","赠送字数","充值时间","备注");
	// 查询数据
	$where['creationtime'] = array("BETWEEN",array(($this->start),($this->end)));
	$medialist = M("media")->field("media_tel username,media_name,mmid,wid,create_time")->select();
	$wemediaincomelist = M("wemediaincome")->where($where)->where("status=1 or status=2 or status=6 or status=11 or status=12")->select();
	$excellist = array();
	// p($medialist);exit();
	// echo count($medialist);exit();
	foreach ($wemediaincomelist as $key => &$value){
		$value['inmoney'] = 0;//充值金额
		$value['grantmoney'] = 0;//赠送金额
		$value['wordnumber'] = 0;//套餐字数
		$value['grantnumber'] = 0;//赠送字数
		foreach ($medialist as $k => &$media) {
			if ($media['wid']==$value['wid']||$media['mmid']==$value['wid']) {
				switch ($value['status']) {
					case '1':if ($media['wid']==$value['wid']) {$value['inmoney']+=$value['money'];$value['username'] = $media['username'];$value['media_name'] = $media['media_name'];}break;
					case '2':if ($media['wid']==$value['wid']) {$value['inmoney']+=$value['money'];$value['username'] = $media['username'];$value['media_name'] = $media['media_name'];}break;
					case '6':if ($media['mmid']==$value['wid']) {$value['wordnumber']+=$value['money'];$value['username'] = $media['username'];$value['media_name'] = $media['media_name'];}break;
					case '11':if ($media['wid']==$value['wid']) {$value['grantmoney']+=$value['money'];$value['username'] = $media['username'];$value['media_name'] = $media['media_name'];}break;
					case '12':if ($media['mmid']==$value['wid']) {$value['grantnumber']+=$value['money'];$value['username'] = $media['username'];$value['media_name'] = $media['media_name'];}break;
				}

			}


		}	// p($media);
		$excellist[$key]['username'] = $value['username'];
		$excellist[$key]['media_name'] = $value['media_name'];
		$excellist[$key]['inmoney'] = $value['inmoney'];
		$excellist[$key]['grantmoney'] = $value['grantmoney'];
		$excellist[$key]['wordnumber'] = $value['wordnumber'];
		$excellist[$key]['grantnumber'] = $value['grantnumber'];
		$excellist[$key]['creationtime'] = $value['creationtime'];
		$excellist[$key]['remark'] = $value['remark'];
	}//exit();
	// p($excellist);exit();
	output_excel($filename.time(),$excelarr,$excellist);
}
    //新的导出自媒体订单列表
    public function theorder(){
        $filename = $this->time."自媒体订单";
        $excelarr = array("订单编号","自媒体名称","标题","订单类型","播报类型","等级","字数","性别","价格","支付方式","背景音","自选","下单时间","接单主播","新人","状态","交单时间");
        // 查询数据
//        $data = json_decode(RedisGet('wechat'),true);
		$start_time = I('post.start')?I('post.start'):$this->start;
		$end_time = I('post.end')? I('post.end'):$this->end;
        $where['pay_time'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
        $where['o.type'] = array("in",[1,2]);
        $orderlist = M("order o")
            ->field('o.id,o.media_id,o.category,o.pay_time,o.title,o.status,o.type,o.user_id,o.words,o.requirement,o.audio_link,o.price,o.increase_price,o.sex,o.pay_status,o.bgm_id,o.sub_type,o.audio_upload_time,o.media_name,s.message level_type,o.pay_type')
            ->join('__SYSTEMSETUP__ s ON o.level = s.id','LEFT')
            ->where($where)
            ->where("(o.status in (5,6,7,8)) and o.sub_type!=1 and o.type!=3")
            ->order('id desc')->select();
        foreach ($orderlist as $key => &$value) {
//            $value['attempts'] = $data[$value['id']]['count'];
            $value['usemoney'] = $value['price'];
            if($value['requirement'] == 3) {
                $value['usemoney'] = $value['price']+$value['increase_price'];
            }
            $value['usemoney'] = $value['bgm_id']!=0?($value['usemoney']*2+20):($value['usemoney']*2);
            switch ($value['pay_type']) {//支付类型 1为微信 2为支付宝 3字数 5为余额
                case '1':$value['paytype'] = "微信";break;
                case '2':$value['paytype'] = "支付宝";break;
                case '3':$value['paytype'] = "字数";break;
                case '5':$value['paytype'] = "余额";break;
                default:$value['paytype'] = "余额";break;
            }
            switch ($value['category']) {
                case '1':$value['category'] = "讲述类";break;
                case '2':$value['category'] = "情感类";break;
                case '3':$value['category'] = "演绎类";break;
                case '4':$value['category'] = "新闻类";break;
                case '5':$value['category'] = "英文类";break;
                default:$value['category'] = "讲述类";break;
            }
            switch ($value['status']){
                case '5':$value['status'] = "已交付未传wav";break;
                case '6':$value['status'] = "已交付未结算";break;
                case '7':$value['status'] = "已评价未传wav";break;
                case '8':$value['status'] = "已交付已结算";break;
            }
            switch ($value['sex']) {
                case '0':$value['sex'] = "全部";break;
                case '1':$value['sex'] = "男";break;
                case '2':$value['sex'] = "女";break;
            }
            switch ($value['type']) {//1为实时单 2为签约单 3为派发单  4为保底单
                case '1':$value['type'] = "实时";break;
                case '2':$value['type'] = "签约";break;
            }

            $excellist[$key]['id'] = $value['id'];
            $excellist[$key]['media_name'] = $value['media_name'];
            $excellist[$key]['title'] = $value['title'];
            $excellist[$key]['type'] = $value['type'];
            $excellist[$key]['category'] = $value['category'];
            $excellist[$key]['level_type'] = $value['level_type'];
            $excellist[$key]['words'] = $value['words'];
            $excellist[$key]['sex'] = $value['sex'];
            $excellist[$key]['usemoney'] = $value['usemoney'];
            $excellist[$key]['paytype'] = $value['paytype'];
            $excellist[$key]['bgm_id'] = $value['bgm_id']==0?"无":"有";
            $excellist[$key]['ischose'] = $value['sub_type']==3?"是":"否";
            $excellist[$key]['pay_time'] =date("Y-m-d H:i:s",$value['pay_time']);
            $excellist[$key]['uuid'] =$value['user_id'];
            $excellist[$key]['newsingle'] = $value['sub_type']==2?"是":"否";
            $excellist[$key]['status'] =$value['status'];
            $excellist[$key]['audio_upload_time'] =date("Y-m-d H:i:s",$value['audio_upload_time']);
        }
        output_excel($filename.time(),$excelarr,$excellist);
    }

    //新的导出自媒体
    public function media_new_old(){
        $start_time = I('post.start')?I('post.start'):$this->start;
        $end_time = I('post.end')? I('post.end'):$this->end;
        $media_where['m.create_time'] = array("BETWEEN",array($start_time,$end_time));
        $filename = $this->time."自媒体列表";
        $excelarr = array("自媒体编号","账号编号","名称","手机号","邮箱","类型","签约权限","剩余字数","签约主播","创建时间","订单数量","订单总金额","首单时间","末单时间","下单频率");
        $media_list = M('media m')
            ->where($media_where)
            ->field('m.id,m.media_name,m.media_tel,m.email,m.articles_name,m.wordnumber,m.uuid,m.create_time,m.wid,u.proportion,u.nickname')
            ->join('__MEMBER__ u on m.uuid = u.uuid','left')
            ->select();
        $order_list = M('order')->field('id,media_id,type,price,requirement,increase_price')->where("status in (5,6,7,8)")->select();
        $order_first_list = M('order')->field('media_id,createtime')->where("status in (5,6,7,8)")->group('media_id')->order("createtime asc")->select();
        $order_end_list = M('order')->field('media_id,createtime')->where("status in (5,6,7,8)")->group('media_id')->order("createtime desc")->select();
        foreach($media_list as $key=>&$vo){
            $vo['count'] = 0;
            $vo['sum'] = 0;
            $vo['frist'] = '';
            $vo['end'] = '';
            foreach ($order_list as $v){
                if($vo['id'] == $v['media_id']){
                    $vo['count'] ++;
                    if($v['type']==1){
                        $vo['sum'] += $v['price'];
                        if($v['requirement'] == 3){
                            $vo['sum'] += $v['increase_price'];
                        }
                        if($v['bgm_id'] != 0){
                            $vo['sum'] += 10;
                        }
                    }
                }
            }
            foreach ($order_first_list as $f){
                if($vo['id'] == $f['media_id']){
                    $vo['frist'] =$f['createtime'];
                }
            }
            foreach ($order_end_list as $e){
                if($vo['id'] == $e['media_id']){
                    $vo['end'] =$e['createtime'];
                }
            }
            $excellist[$key]['id'] = $vo['id'];
            $excellist[$key]['wid'] = $vo['wid'];
            $excellist[$key]['media_name'] = $vo['media_name'];
            $excellist[$key]['media_tel'] = $vo['media_tel'];
            $excellist[$key]['email'] = $vo['email'];
            $excellist[$key]['articles_name'] = $vo['articles_name'];
            $excellist[$key]['proportion'] = $vo['proportion']?$vo['proportion'].'元/百字':'';
            $excellist[$key]['wordnumber'] = $vo['wordnumber'];
            $excellist[$key]['nickname'] = $vo['uuid'] != 0?$vo['nickname'].$vo['uuid']:'无';
            $excellist[$key]['create_time'] = $vo['create_time'];
            $excellist[$key]['count'] = $vo['count'];
            $excellist[$key]['sum'] = ($vo['sum']*2);
            $excellist[$key]['frist'] = $vo['frist']?date("Y-m-d H:i:s",$vo['frist']):'';
            $excellist[$key]['end'] = $vo['frist']?date("Y-m-d H:i:s",$vo['end']):'';
            $excellist[$key]['pin'] = round($vo['count']/floor(($vo['end']-$vo['frist'])/86400));
        }
        output_excel($filename.time(),$excelarr,$excellist);
    }

    //导出主播提现记录表
    public function present_record(){
        $filename = $this->time."提现记录";
        $excelarr = array("主播ID","主播名称","主播手机号","提现金额","订单号","提现时间","账户当前余额");
        // 查询数据
        $where['b.time'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
        $where['b.state'] = 1;
        $present_record_list = M('balance b')
            ->field('b.id,m.uuid,m.nickname,m.username,b.amount,b.trade_no,b.time,m.balance')
            ->where($where)
            ->join('__MEMBER__  m on b.uuid = m.uuid')
            ->select();
        foreach ($present_record_list as $k=>&$v){
            $excellist[$k]['uid'] = $v['uuid'];
            $excellist[$k]['nickname'] = $v['nickname'];
            $excellist[$k]['username'] = $v['username'];
            $excellist[$k]['amount'] = $v['amount'];
            $excellist[$k]['trade_no'] = $v['trade_no'];
            $excellist[$k]['time'] = date('Y-m-d H:i:s',$v['time']);
            $excellist[$k]['balance'] = $v['balance'];
        }
        output_excel($filename.time(),$excelarr,$excellist);
    }

    //新的导出派单列表
    public function sendorder_new()
    {
        $start_time = I('post.start')?I('post.start'):$this->start;
        $end_time = I('post.end')? I('post.end'):$this->end;
        $db_order = M('order');
        $filename = $this->time."派发订单数据";
        $excelarr = array("订单编号","文章编号","订单名称","类型","字数","重要","下单时间","接单主播","交付时间","订单状态");
        // 查询数据
        $where['type'] = array('in',[3,4]);
        $where['pay_time'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
        $orderlist = $db_order->where($where)->order("isreject=1 desc,status=1 desc,id desc")->select();
        foreach($orderlist as $key=>&$v){
            switch ($v['category']){
                case '1': $v['category'] = "评述讲解";break;
                case '2':$v['category'] = "情感演绎";break;
                case '3':$v['category'] = "娱乐互动";break;
                case '4':$v['category'] = "新闻播报";break;
                case '5':$v['category'] = "英文播报";break;
                default:$v['category'] = "未知";
            }
            if($v['type'] == 4  && $v['adminid']!=0){
                $v['user_id'] = M("admin")->where(array("heard_admin_id"=>$v['adminid']))->getField('heard_adminname');
            }elseif($v['status'] == 11){
                $v['user_id'] = array_keys(json_decode(RedisGet('order'.$v['id']),TRUE)['list'])['0'];
            }
            if($v['type'] == 4){//保底单
                switch ($v['status']){
                    case '1':$v['status'] = "保底待抢单";break;
                    case '3':
                        if(($v['pay_time']+900)<time()){
                            $v['status'] = "保底超时未交付";
                        }else{
                            $v['status'] = "保底待交付";
                        }
                        break;
                    case '6':$v['status'] = "已交付未结算";break;
                    case '8':$v['status'] = "已交付已结算";break;
                    default:$v['status'] = "未知";
                }
            }else{
                switch ($v['status']){
                    case '0':$v['status'] = "已作废";break;
                    case '1':$v['status'] = "待抢单";break;
                    case '3':$v['status'] = "待交付";break;
                    case '5':$v['status'] = "已交付未传wav";break;
                    case '6':$v['status'] = "已交付未结算";break;
                    case '7':$v['status'] = "已评价未传wav";break;
                    case '8':$v['status'] = "已交付已结算";break;
                    case '9':$v['status'] = "需要重传";break;
                    case '11':$v['status'] = "主播未接单";break;
                    case '13':$v['status'] = "超时未能完成";break;
                    case '15':$v['status'] = "用户主动取消订单";break;
                    default:$v['status'] = "未知";
                }
            }
            if($v['isimportant'] == 1){
                $v['isimportant'] = "是";
            }else{
                $v['isimportant'] = "否";
            }
            $excellist[$key]['id'] = $v['id'];
            $excellist[$key]['article_id'] = $v['article_id'];
            $excellist[$key]['title'] = $v['title'];
            $excellist[$key]['category'] = $v['category'];
            $excellist[$key]['words'] = $v['words'];
            $excellist[$key]['isimportant'] = $v['isimportant'];
            $excellist[$key]['pay_time'] = date("Y-m-d H:i:s",$v['pay_time']);
            $excellist[$key]['user_id'] = $v['user_id'];
            $excellist[$key]['audio_upload_time'] = $v['audio_upload_time']?date("Y-m-d H:i:s",$v['audio_upload_time']):'';
            $excellist[$key]['status'] = $v['status'];
        }
        output_excel($filename.time(),$excelarr,$excellist);
    }
// 驳回订单
public function rejectorder()
{
	$filename = $this->time."驳回订单数据";
	$excelarr = array("订单编号","自媒体名称","自媒体编号","类型","方式","语速","下单时间","驳回时间","主播收入","主播编号");
	// 查询数据
	$where['date'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$where['o.type'] = 2;
	$orderlist = M("verify_log o")->join('__ORDER__ t ON o.relate_id = t.id','LEFT')->where($where)->field("o.*,t.media_id,t.media_name,t.category,t.type ordertype,t.speed,t.pay_time,t.user_id")->select();
	foreach ($orderlist as $k => &$order){
		switch ($order['ordertype']) {//1为实时单 2为签约单 3为派发单  4为保底单
        case '1':$order['ordertype'] = "实时";break;
        case '2':$order['ordertype'] = "签约";break;
    }
		switch ($order['category']){
        case '1': $order['category'] = "评述讲解";break;
        case '2':$order['category'] = "情感演绎";break;
        case '3':$order['category'] = "娱乐互动";break;
        case '4':$order['category'] = "新闻播报";break;
        case '5':$order['category'] = "英文播报";break;
    }
		switch ($order['speed']) {
			case '1':$order['speed']="正常";break;
			case '2':$order['speed']="快";break;
			case '3':$order['speed']="慢";break;
		}
		$excellist[$k]['relate_id'] = $order['relate_id'];
		$excellist[$k]['media_name'] = $order['media_name'];
		$excellist[$k]['media_id'] = $order['media_id'];
		$excellist[$k]['ordertype'] = $order['ordertype'];
		$excellist[$k]['category'] = $order['category'];
		$excellist[$k]['speed'] = $order['speed'];
		$excellist[$k]['pay_time'] = date("Y-m-d H:i:s",$order['pay_time']);
		$excellist[$k]['date'] = date("Y-m-d H:i:s",$order['date']);
		$excellist[$k]['note'] = $order['note'];
		$excellist[$k]['user_id'] = $order['user_id'];
	}
	output_excel($filename.time(),$excelarr,$excellist);
}
public function rejectorder_old()
{
	$filename = $this->time."驳回订单数据";
	$excelarr = array("订单编号","自媒体名称","自媒体编号","类型","方式","语速","下单时间","驳回时间","主播收入","主播编号");
	// 查询数据
	$where['time'] = array("BETWEEN",array(($this->start),($this->end)));
	$orderlist = M("rejectorderlog o")->join('__THEORDER__ t ON o.oid = t.id','LEFT')->join('__MEDIA__ m on m.id = t.mid','left')->where($where)->field("o.*,t.mid,m.media_name,t.attribute,t.type,t.speed,t.releasetime,t.uuid")->select();
	foreach ($orderlist as $k => &$order){
		switch ($order['attribute']) {
			case '0':$order['attribute']="实时";break;
			case '1':$order['attribute']="签约";break;
		}
		switch ($order['type']) {
			case '0':$order['type']="讲述";break;
			case '1':$order['type']="情感";break;
			case '2':$order['type']="演绎";break;
			case '3':$order['type']="新闻";break;
			case '4':$order['type']="英文";break;
		}
		switch ($order['speed']) {
			case '0':$order['speed']="正常";break;
			case '1':$order['speed']="快";break;
			case '2':$order['speed']="慢";break;
		}
		$excellist[$k]['oid'] = $order['oid'];
		$excellist[$k]['media_name'] = $order['media_name'];
		$excellist[$k]['mid'] = $order['mid'];
		$excellist[$k]['attribute'] = $order['attribute'];
		$excellist[$k]['type'] = $order['type'];
		$excellist[$k]['speed'] = $order['speed'];
		$excellist[$k]['releasetime'] = date("Y-m-d H:i:s",$order['releasetime']);
		$excellist[$k]['time'] = $order['time'];
		$excellist[$k]['passmessage'] = $order['passmessage'];
		$excellist[$k]['uuid'] = $order['uuid'];
	}
	output_excel($filename.time(),$excelarr,$excellist);
}

//主播收入明细表
public function member_income_detail()
{
	$filename = $this->time."主播收入明细数据";
	$excelarr = array("主播编号","主播昵称","订单编号","订单类型","实时单字数","实时单价格（元/百字）","包含：背景音","包含:自选单","包含:预约单","包含:加急单","实时单收入","wav时长（秒）","wav时长（小时）","签约价格(元/小时)","签约收入","派发单收入","奖励","交单时间");
	//查询数据
	$where['o.pay_time'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
	$where['o.status'] = 8;
	$where['o.type'] = array('in',[1,2,3]);
	$where['o.sub_type'] = array('neq',1);
	$order_list = M('order o')
		->field('o.*,m.nickname,s.money userlevel,m.proportion,m.authentication')
		->join('__MEMBER__ m on m.uuid = o.user_id')
		->join('__SYSTEMSETUP__ s ON o.level = s.id','LEFT')
		->where($where)
		->select();
	foreach($order_list as $k=>&$order){
		$order_usermoney 	= 0;//实时单价格
		$proportion 		= 0;//签约单价格
		$signorder_income 	= 0;//签约单收入
		$sendorder_income 	= 0;//派发单收入
		switch ($order['type']){
			case '1' :
				$order['type_name'] = '实时单';
				$order['words'] = '';
				$order['userlevel'] = '';
				//签约单wav时长
				if($order['authentication'] == 1){
					$m_where['type'] = 1;
					$m_where['state'] = 1;
					$m_where['oid'] = $order['id'];
					$order['musictime'] = M('musictime')->where($m_where)->getField('musictime');
				}
			//实时单价格
				$order_usermoney = $order['price'];
				if ($order['requirement'] == 3) {
					$order_usermoney = $order['price']+$order['increase_price'];
				}
				if ($order['bgm_id']!=0) {
					$order_usermoney = $order_usermoney+10;
				}
				break;
			case '2' :
				$order['type_name'] = '签约单';
				//wav时长
				$m_where['type'] = 1;
				$m_where['state'] = 1;
				$m_where['oid'] = $order['id'];
				$order['musictime'] = M('musictime')->where($m_where)->getField('musictime');
				$proportion = $order['proportion']?$order['proportion']:'';
				$signorder_income = $proportion?sprintf("%.2f",($proportion*$order['musictime'])/3600):'';
				break;
			case '3' :
				$order['type_name'] = '派发单';
				$sendorder_income = $order['adminid']!=0?'4':'3';
				break;
		}
		$excellist[$k]['user_id'] = $order['user_id'];
		$excellist[$k]['nickname'] = $order['nickname'];
		$excellist[$k]['id'] = $order['id'];
		$excellist[$k]['type_name'] = $order['type_name'];
		$excellist[$k]['words'] = $order['words'];
		$excellist[$k]['levelmoney'] = $order['userlevel'];
		$excellist[$k]['bgm_id'] = $order['bgm_id']!=0?"是":"否";
		$excellist[$k]['sub_type'] = $order['sub_type']==3?"是":"否";
		$excellist[$k]['requirement2'] = $order['requirement']==2?"是":"否";
		$excellist[$k]['requirement3'] = $order['requirement']==3?"是":"否";
		$excellist[$k]['order_usermoney'] = $order_usermoney?$order_usermoney:'';
		$excellist[$k]['musictime_s'] = $order['musictime']?$order['musictime']:'';
		$excellist[$k]['musictime_h'] = $order['musictime']?$order['musictime']/3600:'';
		$excellist[$k]['proportion'] = $proportion?$proportion:'';//签约价格
		$excellist[$k]['proportion_income'] = $signorder_income?$signorder_income:'';//签约收入
		$excellist[$k]['sendorder_income'] = $sendorder_income?$sendorder_income:'';
		$excellist[$k]['reward'] = '';
		$excellist[$k]['deliver_time'] = $order['audio_upload_time']?date("Y-m-d H:i:s",$order['audio_upload_time']):'';
	}
	output_excel($filename.time(),$excelarr,$excellist);
}

	//主播接单次数
	public function memnber_order()
	{
		$date = date('Ymd',time());
		$filename = $date."导出主播接单表";
		$excelarr = array("主播编号","主播昵称","主播手机号","审核通过时间","已交付定单次数","最后一单状态","最后一单接单时间");
		//查询数据
		//用户交付订单数
		$user_order_list = M('order o')
			->where(['user_id'=>['gt',100001],'o.status'=>['in',[5,6,7,8]]])
			->group('o.user_id')
			->getField('user_id,count(o.id) count',true);
		//用户列表
		$member_list = M('member')
			->field('uuid,nickname,username,creationtime')
			->where(['uuid'=>['gt',100001],'status'=>9])
			->select();
		foreach($member_list as $k=>&$v){
			$v['count'] = $user_order_list[$v['uuid']]?$user_order_list[$v['uuid']]:0;
			$end_order_info = M('order')->field('status,operating_time')->where(['user_id'=>$v['uuid']])->order('operating_time DESC')->find();
			$v['status'] = $end_order_info['status']?$end_order_info['status']:'';
			$v['operating_time'] = $end_order_info['operating_time']?date('Y-m-d H:i:s',$end_order_info['operating_time']):'无';
			switch ($v['status']){//0 订单作废 1 未接单 3 已接单 5 已上传mp3 6 已上传wav或不需要上传wav已完成 7 自媒体已评价未上传wav 8 自媒体评价金额已入帐 9 需要重新上传音频 11 无人接单 订单异常  12 订单异常已处理  13 超时未能完成 15 用户主动取消订单
				case 1: $v['status_name'] ="未接单";break;
				case 3: $v['status_name'] ="已接单";break;
				case 5: $v['status_name'] ="已交付未传wav";break;
				case 6: $v['status_name'] ="已交付未结算";break;
				case 7: $v['status_name'] ="已评价未传wav";break;
				case 8: $v['status_name'] ="已交付已结算";break;
				case 9: $v['status_name'] ="需要重新上传音频";break;
				case 11: $v['status_name'] ="无人接单 订单异常";break;
				case 12: $v['status_name'] ="订单异常已处理";break;
				case 13: $v['status_name'] ="超时未能完成";break;
				case 15: $v['status_name'] ="用户主动取消订单";break;
				default :$v['status_name'] = "无";
			}
			$excellist[$k]['uuid'] = $v['uuid'];
			$excellist[$k]['nickname'] = $v['nickname'];
			$excellist[$k]['username'] = $v['username'];
			$excellist[$k]['creationtime'] = $v['creationtime'];
			$excellist[$k]['count'] = $v['count'];
			$excellist[$k]['status_name'] = $v['status_name'];
			$excellist[$k]['operating_time'] = $v['operating_time'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}
	//导出所有实时单
	public function all_order()
	{
		$filename = $this->time."实时订单数据";
		$db_theorder = M("order");
		$excelarr = array("订单编号","自媒体名称","唯一识别码","订单等级","字数","元/百字","背景音(20元/条)","自选(+20%)","预约(-20%)","加急(+50%)","支付金额","支付中赠送金额","剩余赠送金额","剩余余额","支付方式","支付订单号","下单时间","交单时间","接单主播编号","主播收入","元/百字","订单状态");
		// 查询数据
		$where['pay_time'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
		$orderlist = M("order o")
			->join('__SYSTEMSETUP__ s ON o.level = s.id','LEFT')
			->join('__MEDIA__ m on m.id = o.media_id')
			->where($where)->where("o.type=1 and sub_type!=1")
			->field("o.*,s.message level_type,money userlevel,m.wid")
			->select();
		$w_where["creationtime"] = array("BETWEEN",array($this->start,$this->end));
		$wemediaincome = M('wemediaincome')->where($w_where)->where(["status"=>['in',[3,4,5,13]]])->select();
		$excellist = array();
		// p($orderlist);exit();
		// echo count($orderlist);exit();
		foreach ($orderlist as $k => &$order){
			switch ($order['status']){//订单状态 0 订单作废 1 未接单 3 已接单 5 已上传mp3 6 已上传wav或不需要上传wav已完成 7 自媒体已评价未上传wav 8 自媒体评价金额已入帐 9 需要重新上传音频 11 无人接单 订单异常  12 订单异常已处理  13 超时未能完成 15 用户主动取消订单
				case 0:$order['status_name'] = '订单作废';break;
				case 1:$order['status_name'] = '未接单';break;
				case 3:$order['status_name'] = '已接单';break;
				case 5:$order['status_name'] = '已交付未传wav';break;
				case 6:$order['status_name'] = '已交付未结算';break;
				case 7:$order['status_name'] = '已评价未传wav';break;
				case 8:$order['status_name'] = '已交付已结算';break;
				case 9:$order['status_name'] = '需要重新上传音频';break;
				case 11:$order['status_name'] = '无人接单 订单异常';break;
				case 12:$order['status_name'] = '订单异常已处理';break;
				case 13:$order['status_name'] = '超时未能完成';break;
				case 15:$order['status_name'] = '用户主动取消订单';break;
			}
			$usermoney = $order['price'];
			if ($order['requirement'] == 3) {
				$usermoney = $order['price']+$order['increase_price'];
			}
			if ($order['bgm_id']!=0) {
				$usermoney = $usermoney+10;
			}
			$order['order_number'] = $order['id'];//订单编号
			switch ($order['pay_type']) {
				case '1':
					$order['pay_type_name']="微信";
					$messageobj = json_decode(M('orderqrcode')->where(['order_sn'=>$order['id'],'type'=>1,'paytype'=>1])->getField('message'));
					$order['order_number'] = $messageobj->transaction_id;
					break;
				case '2':
					$order['pay_type_name']="支付宝";
					$messageobj = json_decode(M('orderqrcode')->where(['order_sn'=>$order['id'],'type'=>1,'paytype'=>2])->getField('message'));
					$order['order_number'] = $messageobj->trade_no;
					break;
				case '3':$order['pay_type_name']="字数";break;
				case '5':$order['pay_type_name']="余额";break;
				default:$order['pay_type_name']="余额";break;
			}
			//支付中赠送金额、剩余金额
			foreach($wemediaincome as $w){
				if($order['id'] == $w['oid']){
					if($w['status'] == 13){
						$order['give_money'] = $w['money'];//赠送余额
						$order['give_surplus_money'] = $w['balance'];//赠送剩余余额
					}else if($w['status'] == 3 ||$w['status'] == 4 ||$w['status'] == 5){
						$order['recharge_surplus_money'] = $w['balance'];//剩余余额
					}
				}
			}
			$excellist[$k]['id'] = $order['id'];
			$excellist[$k]['media_name'] = $order['media_name'];
			$excellist[$k]['wid'] = $order['wid'];
			$excellist[$k]['level'] = $order['level_type'];
			$excellist[$k]['words'] = $order['words'];
			$excellist[$k]['levelmoney'] = $order['userlevel']*2;
			$excellist[$k]['bgm_id'] = $order['bgm_id']!=0?"是":"否";
			$excellist[$k]['sub_type'] = $order['sub_type']==3?"是":"否";
			$excellist[$k]['requirement2'] = $order['requirement']==2?"是":"否";
			$excellist[$k]['requirement3'] = $order['requirement']==3?"是":"否";
			$excellist[$k]['paymoney'] = $usermoney*2;
			$excellist[$k]['give_money'] = $order['give_money'];
			$excellist[$k]['give_surplus_money'] = $order['give_surplus_money'];
			$excellist[$k]['recharge_surplus_money'] = $order['recharge_surplus_money'];
			$excellist[$k]['pay_type_name'] = $order['pay_type_name'];//时长
			$excellist[$k]['order_number'] = $order['order_number'];//支付订单号
			$excellist[$k]['pay_time'] = date("Y-m-d H:i:s",$order['pay_time']);
			$excellist[$k]['audio_upload_time'] = $order['audio_upload_time']?date("Y-m-d H:i:s",$order['audio_upload_time']):'';
			$excellist[$k]['user_id'] = $order['user_id'];
			$excellist[$k]['usermoney'] = $usermoney;
			$excellist[$k]['userlevel'] = $order['userlevel'];
			$excellist[$k]['status_name'] = $order['status_name'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//导出所有自媒体的数据
	public function all_media(){//编号、名称、手机号、类型、是否签约、创建时间、下单次数
		$date = date('Ymd',time());
		$filename = $date."导出所有自媒体数据";
		$excelarr = array("编号","名称","手机号","类型","是否签约","创建时间","下单次数");
		$media_data = M('media')->field('id,media_name,media_tel,media_demand,uuid,create_time')->select();
		$order_data = M('order')->where(['status'=>['in',[5,6,7,8]]])->group('media_id')->getField("media_id,count(id)",true);
		foreach ($media_data as $k=>&$v) {
			switch ($v['media_demand']) {
				case '1':$v['media_demand_name'] = "自媒体配音/公众号";break;
				case '2':$v['media_demand_name'] = "短视频配音/视频业";break;
				case '3':$v['media_demand_name'] = "有声书配音/出版业";break;
				case '4':$v['media_demand_name'] = "内容付费配音/文化";break;
				case '5':$v['media_demand_name'] = "教材培训配音/课件";break;
				case '6':$v['media_demand_name'] = "外语配音/英文";break;
				case '7':$v['media_demand_name'] = "模仿配音/名人";break;
			}
			$excellist[$k]['id'] = $v['id']+100000;
			$excellist[$k]['media_name'] = $v['media_name'];
			$excellist[$k]['media_tel'] = $v['media_tel'];
			$excellist[$k]['media_demand_name'] = $v['media_demand_name'];
			$excellist[$k]['uuid'] = $v['uuid']>0?'是':'否';
			$excellist[$k]['create_time'] = $v['create_time'];
			$excellist[$k]['count_order'] = $order_data[$v['id']]?$order_data[$v['id']]:'0';
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//最新的导出自媒体列表
	public function media_new(){
		/***************搜索条件***********************/
		$user_name = I('post.user_name');
		$labelkey = I('post.labelkey');
		$subscript = I('post.subscript')?1:0;
		$start_time = I('post.start')?I('post.start'):$this->start;
		$end_time = I('post.end')? I('post.end'):$this->end;
		$where = " 1=1 ";
		if (!empty($user_name)) {$where .= " and (m.media_tel like '%$user_name%' or m.mmid like '%$user_name%' or m.media_name like '%$user_name%')";}
		if (!empty($labelkey)) {$where .= " and (m.articles like '%$labelkey%')";}
		if (!empty($subscript)) {$where .= " and (m.subscript = 1)";}
		$media_where['m.create_time'] = array("BETWEEN",array($start_time,$end_time));
		/*******************结束*******************/
		$filename = $this->time."自媒体列表";
		$excelarr = array("自媒体编号","账号编号","名称","手机号","邮箱","类型","签约权限","剩余字数","签约主播","创建时间","订单数量","订单总金额","首单时间","末单时间","下单频率");
		$media_list = M('media m')
			->where($where)
			->where($media_where)
			->field('m.id,m.media_name,m.media_tel,m.email,m.articles_name,m.wordnumber,m.uuid,m.create_time,m.wid,u.proportion,u.nickname')
			->join('__MEMBER__ u on m.uuid = u.uuid','left')
			->select();
		$order_list = M('order')->field('id,media_id,type,price,requirement,increase_price')->where("status in (5,6,7,8)")->select();
		$order_first_list = M('order')->where("status in (5,6,7,8)")->group('media_id')->getField('media_id,min(createtime)',true);
		$order_end_list = M('order')->where("status in (5,6,7,8)")->group('media_id')->getField('media_id,max(createtime)',true);
		$new_arr = array();
		foreach ($order_list as $or){
			$new_arr[$or['media_id']][] = $or;
		}
		foreach($media_list as $key=>&$vo){
			$vo['count'] = 0;
			$vo['sum'] = 0;
			$vo['frist'] = '';
			$vo['end'] = '';
			foreach($new_arr[$vo['id']] as $v){
				$vo['count'] ++;
				if($v['type']==1){
					$vo['sum'] += $v['price'];
					if($v['requirement'] == 3){
						$vo['sum'] += $v['increase_price'];
					}
					if($v['bgm_id'] != 0){
						$vo['sum'] += 10;
					}
				}
			}
			$vo['frist'] = $order_first_list[$vo['id']];
			$vo['end'] = $order_end_list[$vo['id']];

			$excellist[$key]['id'] = $vo['id'];
			$excellist[$key]['wid'] = $vo['wid'];
			$excellist[$key]['media_name'] = $vo['media_name'];
			$excellist[$key]['media_tel'] = $vo['media_tel'];
			$excellist[$key]['email'] = $vo['email'];
			$excellist[$key]['articles_name'] = $vo['articles_name'];
			$excellist[$key]['proportion'] = $vo['proportion']?$vo['proportion'].'元/百字':'';
			$excellist[$key]['wordnumber'] = $vo['wordnumber'];
			$excellist[$key]['nickname'] = $vo['uuid'] != 0?$vo['nickname'].$vo['uuid']:'无';
			$excellist[$key]['create_time'] = $vo['create_time'];
			$excellist[$key]['count'] = $vo['count'];
			$excellist[$key]['sum'] = ($vo['sum']*2);
			$excellist[$key]['frist'] = $vo['frist']?date("Y-m-d H:i:s",$vo['frist']):'';
			$excellist[$key]['end'] = $vo['end']?date("Y-m-d H:i:s",$vo['end']):'';
			$excellist[$key]['pin'] = round($vo['count']/floor(($vo['end']-$vo['frist'])/86400));
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//导出主播列表
	public function member(){
		/***************搜索****************/
		$levelkey = I('get.levelkey');
		$sex = I('get.sex');
		$authkey = I('get.authkey');
		$soundtype = I('get.soundtype');
		$user_name = I('get.user_name');
		$where ="m.status = 9";
		if (!empty($user_name)) {$where .= " and (m.nickname like '%$user_name%' or m.uuid like '%$user_name%' or m.username like '%$user_name%')";}
		switch ($authkey) {
			case '1':$where .= " and m.authentication = 1";break;
			case '2':$where .= " and m.authentication = 0";break;
		}
		switch ($sex) {
			case '1':$where .= " and m.sex = 1";break;
			case '2':$where .= " and m.sex = 2";break;
		}
		if (!empty($levelkey)) {
			$leveldatadesc = M("systemsetup")->where(array("type"=>1))->order('value desc')->select();
			foreach ($leveldatadesc as $key1 => $value1) {
				if ($levelkey == $value1['id']) {
					if ($key1 == 0) {
						$where .= " and m.level >=$value1[value]";
					}else{
						$last_value = $leveldatadesc[$key1-1]['value'];
						$where .= " and m.level >=$value1[value] and m.level < $last_value";
					}
				}
			}
		}
		if(!empty($soundtype)){
			$soundtype_arr = explode(',',$soundtype);
			foreach ($soundtype_arr as $v){
				switch ($v){
					case 1: $where .= " and m.soundtype like '%0%'";break;
					case 2: $where .= " and m.soundtype like '%1%'";break;
					case 3: $where .= " and m.soundtype like '%2%'";break;
					case 4: $where .= " and m.soundtype like '%3%'";break;
					case 5: $where .= " and m.soundtype like '%4%'";break;
					case 6: $where .= " and m.soundtype like '%5%'";break;
					case 7: $where .= " and m.is_bgm = 2";break;
				}
			}
		}
		/***************结束***************/
		$filename = $this->time."导出主播列表";
		$excelarr = array("主播编号","昵称","手机号","性别","年龄","籍贯","英语等级","注册日期","讲述类/情感类/演绎类/新闻类","分数","信用分","是否绑定微信","是否认证","是否签约","已提现金额","账户余额","总时长","英文审核状态","背景音审核状态","模仿审核状态","邀请人昵称","完成订单数");
		$member_list = M("member m")
			->where($where)
			->join("__WECHAT__ wc ON m.uuid = wc.uuid","LEFT")
			->join("__DATA__ d ON m.uuid = d.uuid")
			->field("m.uuid,m.nickname,m.username,m.sex,m.age,m.city,m.english,m.creationtime,m.soundtype,m.level,m.score,m.authentication,m.income,m.balance,alwaystime,wc.openid,d.english_audio_type,d.bgm_audio_type,d.imitate_audio_type")
			->order('uuid asc')
			->select();
		$sign_id_count = M('media')->group('uuid')->getField('uuid,count(id)');//用户id和签约数
		$member_id_name = M('member')->getField('uuid,nickname',true);//用户id和昵称
		$invite = M('invite')->getField('uuid,inviteid',true);//用户邀请数据 [用户id=>邀请人id]
		$order_data = M('order')->where(['status'=>['in',[7,8]]])->group('user_id')->getField('user_id,count(id)',true);//订单数
		foreach($member_list as $key=>&$v){
			switch ($v['english']) {
				case 0:$v['english_l'] = "入门";break;
				case 1:$v['english_l'] = "GET4";break;
				case 2:$v['english_l'] = "GET6";break;
				case 3:$v['english_l'] = "专业6级";break;
				case 4:$v['english_l'] = "专业8级";break;
				case 5:$v['english_l'] = "母语";break;
			}
			switch ($v['level']) {
				case $v['level'] > 80:$v['level'] = '(A)'.$v['level'];break;
				case $v['level'] > 50:$v['level'] = '(B)'.$v['level'];break;
				default:$v['level'] = '(C)'.$v['level'];break;
			}
			switch ($v['english_audio_type']) {
				case 1:$v['english_audio_type'] = "已通过";break;
				case 2:$v['english_audio_type'] = "审核中";break;
				default:$v['english_audio_type'] = "未通过";break;
			}
			switch ($v['bgm_audio_type']) {
				case 1:$v['bgm_audio_type'] = "已通过";break;
				case 2:$v['bgm_audio_type'] = "审核中";break;
				default:$v['bgm_audio_type'] = "未通过";break;
			}
			switch ($v['imitate_audio_type']) {
				case 1:$v['imitate_audio_type'] = "已通过";break;
				case 2:$v['imitate_audio_type'] = "审核中";break;
				default:$v['imitate_audio_type'] = "未通过";break;
			}
			$v['inviteid'] = $invite[$v['uuid']];
			$v['invitename'] = $v['inviteid']?$member_id_name[$v['inviteid']]:"无";
			$v['soundtype_arr'] = explode(",", $v['soundtype']);
			$v['soundtype_arr1'] = str_replace(array(0,1,2,3,4,5,6),array("讲述类","情感类","演绎类","新闻类","英文类","自选","背景音"),$v['soundtype_arr']);
			$v['soundtype_str'] = implode(",", $v['soundtype_arr1']);
			$excellist[$key]['uuid'] = $v['uuid'];
			$excellist[$key]['nickname'] = $v['nickname'];
			$excellist[$key]['username'] = $v['username'];
			$excellist[$key]['sex'] = $v['sex'] == 1 ? '男' : '女';
			$excellist[$key]['age'] = $v['age'];
			$excellist[$key]['city'] = $v['city'];
			$excellist[$key]['english_l'] = $v['english_l'];
			$excellist[$key]['creationtime'] = $v['creationtime'];
			$excellist[$key]['soundtype_str'] = $v['soundtype_str'];
			$excellist[$key]['level'] = $v['level'];
			$excellist[$key]['score'] = $v['score'];
			$excellist[$key]['openid'] = $v['openid'] == '' ? '未绑定' : '已绑定';
			$excellist[$key]['authentication'] = $v['authentication'] == 1 ? '是' : '否';
			$excellist[$key]['iscollect'] = $sign_id_count[$v['uuid']] > 0 ? '是' : '否';
			$excellist[$key]['already_presented'] = $v['income']-$v['balance'];
			$excellist[$key]['balance'] = $v['balance'];
			$excellist[$key]['alwaystime'] = $v['alwaystime'];
			$excellist[$key]['english_audio_type'] = $v['english_audio_type'];
			$excellist[$key]['bgm_audio_type'] = $v['bgm_audio_type'];
			$excellist[$key]['imitate_audio_type'] = $v['imitate_audio_type'];
			$excellist[$key]['invitename'] = $v['invitename'];
			$excellist[$key]['ordercount'] = $order_data[$v['uuid']]?$order_data[$v['uuid']]:'0';
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//订单抢单人数
	public function order_join_number(){
		$Redis = RedisNew();
		$filename = $this->time."实时订单数据";
		$start_time = I('post.start')?I('post.start'):$this->start;
		$end_time = I('post.end')? I('post.end'):$this->end;
		$excelarr = array("订单编号","抢单人数","抢单人ID","下单时间");
		$where['pay_time'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
		$where['type'] = 1;
		$order_lists1 = M('order')->where($where)->select();
		foreach ($order_lists1 as $k=> &$order){
			$order_lists = json_decode($Redis->get('order'.$order['id']),TRUE);
			$order_list = $order_lists['list'];
			$order['conut'] = 0;
			$order['uuid'] = '';
			foreach ($order_list as $key => &$vo) {
				if($vo == 1){
					$order['conut']++;
					$order['uuid'] .= $key .',';
				}
			}
			$excellist[$k]['oid'] = $order['id'];
			$excellist[$k]['count'] = $order['conut'];
			$excellist[$k]['uuid'] = $order['uuid'];
			$excellist[$k]['time'] = date('Y-m-d H:i:s',$order['pay_time']);
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//下单未付款
	public function order_nopay(){
		$filename = $this->time."下单未付款数据";
		$start_time = "2018-08-01";
		$end_time = "2018-09-01";
		$excelarr = array("订单编号","自媒体名称","金额","背景音","自选","加急","创建时间","订单类型");
		$where['pay_status'] = 2;
		$where['type'] = ['in',[1,2]];
		$where['createtime'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
		$order_nopay_list = M('order')->field('id,media_name,words,price,bgm_id,increase_price,requirement,sub_type,createtime,type')->where($where)->select();
		foreach($order_nopay_list as $key=>&$order){
			$usermoney = $order['price'];
			if ($order['requirement'] == 3) {
				$usermoney = $order['price']+$order['increase_price'];
			}
			if ($order['bgm_id']!=0) {
				$order['bgm'] = '有';
				$usermoney = $usermoney+10;
			}else{
				$order['bgm'] = '无';
			}
			$usermoney = $usermoney*2;
			switch ($order['type']){
				case 1: $order['type_name'] = '实时单';break;
				case 2:
					$order['type_name'] = '签约单';
					$usermoney = $order['words']."字";
					break;
				default: $order['type_name'] = '未知';
			}
			$excellist[$key]['oid'] = $order['id'];
			$excellist[$key]['media_name'] = $order['media_name'];
			$excellist[$key]['paymoney'] = $usermoney;
			$excellist[$key]['bgm'] = $order['bgm'];
			$excellist[$key]['zixuan'] = $order['sub_type']==3?'是':'否';
			$excellist[$key]['jiaji'] = $order['requirement']==3?'是':'否';
			$excellist[$key]['createtime'] = date('Y-m-d H:i:s',$order['createtime']);
			$excellist[$key]['type_name'] = $order['type_name'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//自选退款
	public function order_drawback(){
		$filename = $this->time."自选退款";
		$start_time = "2018-08-01";
		$end_time = "2018-09-01";
		$excelarr = array("订单编号","自媒体名称","金额","背景音","加急","创建时间","订单类型","订单状态");
		$where['sub_type'] = 3;
		$where['type'] = ['in',[1,2]];
		$where['refundtype'] = 2;
		$where['createtime'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
//		$wemedia_drawback_list = M('wemediaincome')->where(['status'=>['in',[8,9,10]]])->select();
//		$media_arr = array();
//		foreach($wemedia_drawback_list as $we){
//			$media_arr[$we['oid']] = $we;
//		}
		$order_drawback_list = M('order')
			->field('id,media_name,words,price,bgm_id,increase_price,requirement,sub_type,createtime,status,type')
			->where($where)->select();
		foreach($order_drawback_list as $key=>&$order){
			$usermoney = $order['price'];
			if ($order['requirement'] == 3) {
				$usermoney = $order['price']+$order['increase_price'];
			}
			if ($order['bgm_id']!=0) {
				$order['bgm'] = '有';
				$usermoney = $usermoney+10;
			}else{
				$order['bgm'] = '无';
			}
			$usermoney = $usermoney*2;
			switch ($order['type']){
				case 1: $order['type_name'] = '实时单';break;
				case 2:
					$order['type_name'] = '签约单';
					$usermoney = $order['words']."字";
					break;
				default: $order['type_name'] = '未知';
			}
			switch($order['status']){
				case 11:$order['status_name'] = '未有主播接单';break;
				case 12:$order['status_name'] = '未有主播接单';break;
				case 13:$order['status_name'] = '主播超时未交';break;
				default:$order['status_name'] = '';
			}
			$excellist[$key]['oid'] = $order['id'];
			$excellist[$key]['media_name'] = $order['media_name'];
			$excellist[$key]['paymoney'] = $usermoney;
			$excellist[$key]['bgm'] = $order['bgm'];
			$excellist[$key]['jiaji'] = $order['requirement']==3?'是':'否';
			$excellist[$key]['createtime'] = date('Y-m-d H:i:s',$order['createtime']);
			$excellist[$key]['type_name'] = $order['type_name'];
			$excellist[$key]['status_name'] = $order['status_name'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//8月自媒体下单数
	public function media_order_num(){
		$filename = "8月实时主动取消单数";
		$start_time = "2018-08-01";
		$end_time = "2018-09-01";
		$where['createtime'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
		$where['status'] = 15;
		$where['type'] = 1;
		$choshi = M('order')->field('count(content) num,add_time')->where($where)->group('add_time')->select();
		$arr = array();
		foreach($choshi as $key1=>&$v1){
			$arr[substr($v1['add_time'],'0','8')][] = $v1;
		}
		$excelarr = array("日期","单数");
		foreach($arr as $key=>&$v){
			$i = 0;
			$v['num'] = 0;
			foreach ($v as $i){
				$v['num'] += $i['num'];
			}
			$excellist[$i]['date'] = $key;
			$excellist[$i]['num'] = $v['num'];
			$i++;
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	public function media_order_num1(){
		$filename = "8月签约主动取消单数";
		$start_time = "2018-08-01";
		$end_time = "2018-09-01";
		$where['createtime'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
		$where['status'] = 15;
		$where['type'] = 2;
		$choshi = M('order')->field('count(content) num,add_time')->where($where)->group('add_time')->select();
		$excelarr = array("日期","单数");
		foreach($choshi as $key=>&$v){
			$excellist[$key]['date'] = $v['add_time'];
			$excellist[$key]['num'] = $v['num'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	public function media_order_num2(){
		$filename = "8月实时超时单数";
		$start_time = "2018-08-01";
		$end_time = "2018-09-01";
		$where['createtime'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
		$where['status'] = ['in',[11,12]];
		$where['type'] = 1;
		$choshi = M('order')->field('count(content) num,add_time')->where($where)->group('add_time')->select();
		$excelarr = array("日期","单数");
		foreach($choshi as $key=>&$v){
			$excellist[$key]['date'] = $v['add_time'];
			$excellist[$key]['num'] = $v['num'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	public function media_order_num3(){
		$filename = "8月签约超时单数";
		$start_time = "2018-08-01";
		$end_time = "2018-09-01";
		$where['createtime'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
		$where['status'] = ['in',[11,12]];
		$where['type'] = 2;
		$choshi = M('order')->field('count(content) num,add_time')->where($where)->group('add_time')->select();
		$excelarr = array("日期","单数");
		foreach($choshi as $key=>&$v){
			$excellist[$key]['date'] = $v['add_time'];
			$excellist[$key]['num'] = $v['num'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//转单记录
	public function signreorder(){
		$filename = "8月签约单转单数";
		$start_time = "2018-08-01";
		$end_time = "2018-09-01";
		$where['time'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
		$res = M('signreorderlog')->field('oid,count(id) count,time')->where($where)->group('oid')->select();
		$excelarr = array("订单ID","转单时间","转单次数");
		foreach($res as $key=>&$v){
			$excellist[$key]['oid'] = $v['oid'];
			$excellist[$key]['count'] = $v['count'];
			$excellist[$key]['time'] = date('Y-m-d H:i:s',$v['time']);
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//余额充值明细
	public function balance_recharge_detail(){
		$filename = $this->time."余额充值明细";
		$start_time = I('post.start')?I('post.start'):$this->start;
		$end_time = I('post.end')?I('post.end'):$this->end;
		$excelarr = array("充值单号","手机号","类型","充值金额","赠送金额","支付方式","交易流水号","时间");
//		$where['creationtime'] = array("BETWEEN",array($start_time,$end_time));
		$where['i.status'] = ['in',[1,2]];
		$wemediaincome_list = M('wemediaincome i')
			->join('__WEMEDIA__ w on i.wid = w.wid','left')
			->join('__BALANCEQRCODE__ b on i.number = b.number','left')
			->field("w.username,w.wid,i.id,i.creationtime,i.status,i.money,i.number,b.money b_money,b.reward,b.message")
			->where($where)
			->select();
		//取出所有后台充值赠送
		$grant_list = M('wemediaincome')->where('status=11')->getField('number,money',true);
		foreach($wemediaincome_list as $key=>&$value){
			switch($value['status']){
				case 1:
					$value['money'] = $value['b_money']/100;
					$value['grantmoney'] = $value['reward']/100;
					$value['serial'] = json_decode($value['message'])->trade_no;
					$value['status'] = '支付宝充值';
					break;
				case 2:
					$value['grantmoney'] = $grant_list[$value['number']]?$grant_list[$value['number']]:0;
					$value['status'] = '后台充值';
					$value['serial'] = $value['number'];
					break;
			}
			$excellist[$key]['id'] = $value['id'];
			$excellist[$key]['username'] = $value['username'];
			$excellist[$key]['type'] = $value['status'];
			$excellist[$key]['money'] = $value['money'];
			$excellist[$key]['grantmoney'] = $value['grantmoney'];
			$excellist[$key]['status'] = $value['status'];
			$excellist[$key]['serial'] = $value['serial'];
			$excellist[$key]['creationtime'] = $value['creationtime'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//签约充值明细
	public function sign_recharge_detail(){
		$filename = $this->time."签约充值明细";
		$start_time = I('post.start')?I('post.start'):$this->start;
		$end_time = I('post.end')?I('post.end'):$this->end;
		$excelarr = array("充值单号","自媒体名称","自媒体编号","手机号","类型","充值字数","赠送字数","签约金额","签约套餐","交易状态","支付方式","交易流水号","时间");
		$where['UNIX_TIMESTAMP(creationtime)'] = array("BETWEEN",array(strtotime($start_time),strtotime($end_time)));
		$where['i.status'] = ['in',[6,13]];
		$wemediaincome = M('wemediaincome i');
		$income_list = $wemediaincome
			->join('__MEDIA__ m ON i.mid = m.mmid')
			->order("i.id desc")
			->join('__WORDQRCODE__ b on i.number = b.number','left')
			->field("m.media_tel,m.media_name,m.mmid,m.wid,i.id,i.creationtime,i.status,i.money,i.grantmoney,i.signmoney,i.sign_payment,i.serial_number,i.number,b.message")
			->where($where)
			->select();
		//取出status为12的（老的后台赠送的分两条存的）
//		$old_grant = $wemediaincome->where(['status'=>12])->getField('number,money',true);
		foreach ($income_list as $key=>&$value){
//			if($old_grant[$value['number']]){
//				$value['grantmoney'] = $old_grant[$value['number']];
//			}
			switch ($value['status']){
				case 6:
					if($value['sign_payment'] == 1){
						$value['sign_payment'] = '支付宝';
					}elseif($value['sign_payment'] == 2){
						$value['sign_payment'] = '对公转账';
					}
					$value['status'] = "后台充值";
					break;
				case 13:
					$value['sign_payment']='支付宝';
					$value['status'] = "支付宝充值";
					$value['serial_number'] = json_decode($value['message'])->trade_no;
					break;
			}
			$excellist[$key]['id'] = $value['id'];
			$excellist[$key]['media_name'] = $value['media_name'];
			$excellist[$key]['mmid'] = $value['mmid'];
			$excellist[$key]['media_tel'] = $value['media_tel'];
			$excellist[$key]['status'] = $value['status'];
			$excellist[$key]['money'] = $value['money'];
			$excellist[$key]['grantmoney'] = $value['grantmoney'];
			$excellist[$key]['signmoney'] = $value['signmoney'];
			$excellist[$key]['qytc'] = '';
			$excellist[$key]['jystatus'] = '';
			$excellist[$key]['sign_payment'] = $value['sign_payment'];
			$excellist[$key]['serial_number'] = $value['serial_number'];
			$excellist[$key]['creationtime'] = $value['creationtime'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//自媒体体扣字数记录
	public function media_user_word(){
		$filename = $this->time."自媒体消耗字数";
		$start_time = I('post.start')?I('post.start'):$this->start;
		$end_time = I('post.end')?I('post.end'):$this->end;
		$excelarr = array("订单编号","自媒体名称","自媒体编号","订单字数","下单时间");
//		(o.status=8) and o.type=2 and sub_type!=1
		$where['status'] = ['neq',0];
		$where['type'] = 2;
		$where['sub_type'] = ['neq',1];
		$where['createtime'] = array("BETWEEN",array(strtotime($this->start),strtotime($this->end)));
		$list = M('order')->where($where)->select();
		foreach ($list as $key=>&$value){
			$excellist[$key]['id'] = $value['id'];
			$excellist[$key]['media_name'] = $value['media_name'];
			$excellist[$key]['media_id'] = $value['media_id'];
			$excellist[$key]['words'] = $value['words'];
			$excellist[$key]['createtime'] = date('Y-m-d H:i:s',$value['createtime']);
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}

	//所有自媒体的剩余字数
	public function all_media_words(){
		$filename = $this->time."自媒体剩余字数";
		$list = M('media')->field('mmid,media_name,wordnumber,grantnumber')->select();
		$excelarr = array("自媒体名称","自媒体编号","剩余字数","剩余赠送字数");
		foreach ($list as $key=>&$value){
			$excellist[$key]['media_name'] = $value['media_name'];
			$excellist[$key]['mmid'] = $value['mmid'];
			$excellist[$key]['wordnumber'] = $value['wordnumber'];
			$excellist[$key]['grantnumber'] = $value['grantnumber'];
		}
		output_excel($filename.time(),$excelarr,$excellist);
	}
/*-----------------------------------------------------------------*/

/*---------------------*/
}